package JDBC_Test.book.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import JDBC_Test.book.JDBCUtil;
import JDBC_Test.book.Model.BookStockModel;

public class BookStockDao {

	Connection con = null;
	Statement st = null;
	PreparedStatement ps = null;

	/**
	 * 进书时更新库存
	 * 
	 * @param list
	 * @throws SQLException
	 */
	public void update(List<BookStockModel> list) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "update book_stock set book_stock = ? where book_no = ?";

		try {
			con = JDBCUtil.geConnection();
			con.setAutoCommit(false);
			ps = con.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				// 查出原库存容量
				BookStockModel bookStockModel = list.get(i);
				String sqlquery = "select book_stock from book_stock where book_no = '" + list.get(i).getBookNo() + "'";
				PreparedStatement ps1 = con.prepareStatement(sqlquery);
				ResultSet rs = ps1.executeQuery();
				int oldStock = 0;
				while (rs.next()) {
					oldStock = rs.getInt("book_stock");
				}
				if (oldStock == 0) {
					String sqlinsert = "insert into book_stock (book_no,book_stock) values (?,?)";
					PreparedStatement ps2 = con.prepareStatement(sqlinsert);
					ps2.setString(1, bookStockModel.getBookNo());
					ps2.setInt(2, bookStockModel.getBookStock());
					ps2.executeUpdate();
				} else {
					// 更新库存容量
					int newStock = list.get(i).getBookStock() + oldStock;
					ps.setInt(1, newStock);
					ps.setString(2, list.get(i).getBookNo());
					ps.addBatch();
				}

			}
			ps.executeBatch();
			ps.clearBatch();
			con.commit();

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);

		}

	}

}
